package com.googlecode.garbagecan.springsecuritystudy.util;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.apache.commons.io.FileUtils;

public class DBUtil {

	public static void main(String[] args) throws Exception {
		DBUtil dbUtil = new DBUtil();
		dbUtil.initDB();
		dbUtil.testDB();
	}

	// Before run this method, need to delete hsqldb folder first.
	public void initDB() throws Exception {
		Class.forName("org.hsqldb.jdbcDriver");
		Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:.", "sa", "");
		String content = FileUtils.readFileToString(new File(DBUtil.class.getResource("/db.sql").getFile()));
		String[] sqls = content.split(";");
		for (String sql : sqls) {
			if (sql.trim().length() != 0) {
				PreparedStatement stmt = conn.prepareStatement(sql);
				stmt.executeUpdate();
				stmt.close();
			}
		}
		conn.commit();
		conn.close();
	}

	public void testDB() throws Exception {
		Class.forName("org.hsqldb.jdbcDriver");
		Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:.", "sa", "");
		PreparedStatement stmt = conn.prepareStatement("select * from users");
		ResultSet rs = stmt.executeQuery();
		while(rs.next()) {
			System.out.println(rs.getObject("username"));
		}
		rs.close();
		stmt.close();
		
		conn.close();
	}
}

